
/* ----------------------------------------------------------------------------
* Validate library_id
-------------------------------------------------------------------------------*/

DROP FUNCTION IF EXISTS main.validate_library_id();
CREATE OR REPLACE FUNCTION main.validate_library_id()
  RETURNS trigger AS
$BODY$
    DECLARE 
    invalid_library_id text; -- This variable will store output of queries cheking for invalid library_id. It must remain NULL for valid IDs;
    
    BEGIN

    -- library_id must contain only letters, numbers and  underscore.
    SELECT library_id INTO invalid_library_id FROM libraries WHERE library_id !~ '(^[a-zA-Z]\w+[a-zA-Z0-9]$)';
    
    IF invalid_library_id IS NOT NULL THEN
        RAISE EXCEPTION 'Trigger validate_library_id: Invalid library_id format: "%" should contain only letters, numbers and underscores.', invalid_library_id;
        RETURN NULL;
    END IF;

    RETURN NULL;
    END;
$BODY$
  LANGUAGE plpgsql IMMUTABLE;

/* ----------------------------------------------------------------------------- */
drop trigger if exists trigger_validate_library_id on libraries;
create constraint trigger trigger_validate_library_id  after insert or update
    on libraries deferrable initially deferred for each row
    execute procedure validate_library_id();


/*
* UNIT TEST:
* set return type to text;
* ---------
*/

begin; -- Fail due to dot
update libraries set library_id = 'db001.test_do_NOT_delete_me' where sample_id = 'DB001_test_do_NOT_delete_me';
select validate_library_id();
rollback;

begin; -- Fail
update libraries set library_id = 'db001$test_do_NOT_delete_me' where sample_id = 'DB001_test_do_NOT_delete_me';
select validate_library_id();
rollback;

begin; -- Fail
update libraries set library_id = 'db001_test_do_NOT_delete_me_' where sample_id = 'DB001_test_do_NOT_delete_me';
select validate_library_id();
rollback;

begin; -- Fail
update libraries set library_id = '_db001_test_do_NOT_delete_me' where sample_id = 'DB001_test_do_NOT_delete_me';
select validate_library_id();
rollback;

begin; -- Ok
update libraries set library_id = 'db001_test_do_NOT_delete_me_2' where sample_id = 'DB001_test_do_NOT_delete_me';
select validate_library_id();
rollback;

------------------------ END OF UNIT TEST -------------------------------------    
